use crate::base::{Error, Result};
use crate::prelude::{
    get_my_pool,Column, ColumnCell, ColumnCellBranch, ColumnCellLeaf, ColumnCells, ImportProps,
};
use std::collections::HashMap;
use std::io::Cursor;
use sqlx::Row;
use super::export::ExcelWriter;

pub fn get_import_template(imp_conf: &ImportProps, cols: &Vec<Column>) -> Result<Vec<u8>> {
    if let Some(file_name) = &imp_conf.template_name {
        let conf_path =
            std::env::var("SHQ_PAS_CONF_PATH").unwrap_or("./template_files".to_string());
        let file_path = format!("{}/{}", conf_path, file_name);
        let file = std::fs::read(file_path)
            .or_else(|_err| Err(Error::from(format!("文件{}读取失败", file_name))))?;
        return Ok(file);
    }
    let col_cells = create_header_cells(cols, &imp_conf.col_map);
    let mut ew = ExcelWriter::new(col_cells)?;
    let sheet_name = "Sheet1";
    ew.create_header(sheet_name, false, None, None)?;
    ew.to_bytes()
}

fn create_header_cells(cols: &Vec<Column>, imp_col_map: &HashMap<String, String>) -> ColumnCells {
    let mut max_deep: u8 = 0;
    let mut deeps: Vec<u8> = Vec::new();
    for col in cols.iter() {
        let deep = get_column_deep(col, &imp_col_map);
        deeps.push(deep);
        if deep > max_deep {
            max_deep = deep;
        }
    }
    let mut trs: Vec<Vec<ColumnCell>> = Vec::new();
    let mut leafs: Vec<(usize, usize)> = Vec::new();
    (0..max_deep).into_iter().for_each(|_| trs.push(Vec::new()));
    for (i, col) in cols.iter().enumerate() {
        get_header_cells(
            col,
            0,
            deeps[i],
            max_deep,
            &mut trs,
            &mut leafs,
            &imp_col_map,
        );
    }
    ColumnCells::from(trs, leafs)
}

fn get_header_cells(
    col: &Column,
    row_number: usize,
    deep: u8,
    max_deep: u8,
    cells: &mut Vec<Vec<ColumnCell>>,
    leafs: &mut Vec<(usize, usize)>,
    imp_col_map: &HashMap<String, String>,
) {
    let leafs_count = get_column_leafs_count(col, imp_col_map);
    if leafs_count == 0 {
        return;
    }
    let mut rowspan = 1_u8;
    let is_leaf = col.children.is_empty();
    if is_leaf && deep < max_deep {
        rowspan += max_deep - deep;
    }
    let cell = if col.children.len() > 0 {
        ColumnCell::Branch(ColumnCellBranch {
            title: col.title.clone().unwrap_or("".to_string()),
            children: Vec::new(),
            colspan: leafs_count as u8,
            rowspan,
        })
    } else {
        let mut unit: f64 = col.unit.to_u32() as f64;
        if let Some(fmt) = col.list_props.as_ref().and_then(|lp| lp.format.as_ref()) {
            if fmt.ends_with('%') {
                unit = unit / 100.0;
            }
        }
        ColumnCell::Leaf(ColumnCellLeaf {
            name: col.name.clone().unwrap_or("".to_string()),
            title: col.title.clone(),
            colspan: leafs_count as u8,
            rowspan,
            data_type: col.data_type.clone(),
            list_props: col.list_props.clone(),
            dict_col_name: col.dict_col_name.clone(),
            front_must: false,
            is_null: col.is_null,
            default_filter: col.default_filter.clone(),
            param_selected: None,
        })
    };
    cells[row_number].push(cell);
    for i in 1..rowspan {
        cells[row_number + i as usize].push(ColumnCell::None);
    }
    if is_leaf {
        leafs.push((row_number, cells[row_number].len() - 1));
    }
    for ccol in col.children.iter() {
        get_header_cells(
            ccol,
            row_number + 1,
            deep,
            max_deep,
            cells,
            leafs,
            imp_col_map,
        );
    }
}

fn is_display_column(col: &Column, imp_col_map: &HashMap<String, String>) -> bool {
    if col.children.is_empty() {
        if let Some(name) = col.name.as_ref() {
            return imp_col_map.get(name).is_some();
        }
    }
    for ccol in col.children.iter() {
        let is_display = is_display_column(ccol, imp_col_map);
        if is_display {
            return is_display;
        }
    }
    false
}

fn get_column_deep(col: &Column, imp_col_map: &HashMap<String, String>) -> u8 {
    if !is_display_column(col, imp_col_map) {
        return 0;
    }
    if col.children.is_empty() {
        return 1;
    }
    let mut deep = 0_u8;
    for ccol in col.children.iter() {
        let d2 = get_column_deep(ccol, imp_col_map);
        if d2 > deep {
            deep = d2;
        }
    }
    deep + 1
}

fn get_column_leafs_count(col: &Column, imp_col_map: &HashMap<String, String>) -> usize {
    if !is_display_column(col, imp_col_map) {
        return 0;
    }
    let mut count = 0_usize;
    if col.children.is_empty() {
        return 1;
    }
    for ccol in col.children.iter() {
        count += get_column_leafs_count(ccol, imp_col_map);
    }
    count
}

pub async fn get_home_visit_import_template(imp_conf: String,uid: u64) -> Result<Vec<u8>> {
    let path = std::path::Path::new(&imp_conf);
    if !path.exists() {
        return Err(format!("文件路径不存在: {}", imp_conf).into());
    }

    //A2替换的内容
    let sql = "SELECT concat('工号：',a.no,'            ','姓名：',a.name,'            ','政治面貌：',ifnull(political,''),'            ',
                    '所在单位：',b.name,'            ','职务/岗位：',ifnull(gw,''),'            ',
                    '用工性质：',case when prop_id = 1 then '正式' when prop_id=2 then '派遣' when prop_id=3 then '外包' when prop_id=4 then '虚拟' else '' end 
                    ) as a2 
                    FROM  sys_user a inner join sys_organization b on a.organ_id = b.id where a.id = ?";
    let row = sqlx::query(sql)
    .bind(uid)
    .fetch_optional(get_my_pool())
    .await
    .map_err(|e| format!("查询用户信息失败：{}", e))?;

    let row = row.unwrap();
    let a2: String = row.try_get("a2")?;

    let mut book = umya_spreadsheet::reader::xlsx::read(path)
        .map_err(|e| format!("读取Excel文件失败（路径：{}）：{}", imp_conf, e))?;
    book.get_sheet_by_name_mut("Sheet1").unwrap().get_cell_mut("A2").set_value(a2);

    let mut excel_bytesf = Cursor::new(Vec::new());

    umya_spreadsheet::writer::xlsx::write_writer(
        &book,
        excel_bytesf.get_mut(),
    ).or_else(
            |err| {
                let err_msg = format!("excel转bytes出错:{}", err);
                Err(Error::Other(err_msg))
            },
        )?;
    Ok(excel_bytesf.into_inner())
}